/* ---------------------------------------------------------------------- */
/* Script generated with: DeZign for Databases v6.3.2                     */
/* Target DBMS:           MS SQL Server 2008                              */
/* Project file:          Quizz.dez                                       */
/* Project name:                                                          */
/* Author:                                                                */
/* Script type:           Database creation script                        */
/* Created on:            2012-10-16 11:52                                */
/* ---------------------------------------------------------------------- */


/* ---------------------------------------------------------------------- */
/* Tables                                                                 */
/* ---------------------------------------------------------------------- */

/* ---------------------------------------------------------------------- */
/* Add table "Property"                                                   */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Property] (
    [Id] BIGINT IDENTITY(0,1) NOT NULL,
    [Type] INTEGER NOT NULL,
    [Value] NTEXT,
    [Name] NVARCHAR(256),
    [Title] NVARCHAR(128),
    CONSTRAINT [PK_Property] PRIMARY KEY ([Id])
)
GO


/* ---------------------------------------------------------------------- */
/* Add table "LoginLog"                                                   */
/* ---------------------------------------------------------------------- */

CREATE TABLE [LoginLog] (
    [Id] BIGINT IDENTITY(0,1) NOT NULL,
    [SessionId] NVARCHAR(50),
    [UserId] BIGINT NOT NULL,
    [LoginTime] DATETIME NOT NULL,
    [LogoutTime] DATETIME,
    [IsOnline] BIT NOT NULL,
    CONSTRAINT [PK_LoginLog] PRIMARY KEY ([Id])
)
GO


/* ---------------------------------------------------------------------- */
/* Add table "Subject"                                                    */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Subject] (
    [Id] BIGINT IDENTITY(0,1) NOT NULL,
    [Name] NVARCHAR(512),
    [Description] NTEXT,
    [Status] INTEGER NOT NULL,
    CONSTRAINT [PK_Subject] PRIMARY KEY ([Id])
)
GO


/* ---------------------------------------------------------------------- */
/* Add table "Test"                                                       */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Test] (
    [Id] BIGINT IDENTITY(0,1) NOT NULL,
    [Title] NVARCHAR(512),
    [Description] NVARCHAR(1024),
    [Note] NVARCHAR(512),
    [Type] INTEGER NOT NULL,
    [ShareType] INTEGER NOT NULL,
    [Status] INTEGER NOT NULL,
    [Level] INTEGER NOT NULL,
    [TestTime] FLOAT NOT NULL,
    CONSTRAINT [PK_Test] PRIMARY KEY ([Id])
)
GO


/* ---------------------------------------------------------------------- */
/* Add table "TestSubject"                                                */
/* ---------------------------------------------------------------------- */

CREATE TABLE [TestSubject] (
    [Id] BIGINT IDENTITY(0,1) NOT NULL,
    [SubjectId] BIGINT NOT NULL,
    [TestId] BIGINT NOT NULL,
    CONSTRAINT [PK_TestSubject] PRIMARY KEY ([Id])
)
GO


/* ---------------------------------------------------------------------- */
/* Add table "Role"                                                       */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Role] (
    [Id] BIGINT IDENTITY(0,1) NOT NULL,
    [Name] NVARCHAR(128),
    CONSTRAINT [PK_Role] PRIMARY KEY ([Id])
)
GO


/* ---------------------------------------------------------------------- */
/* Add table "Group"                                                      */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Group] (
    [Id] BIGINT IDENTITY(0,1) NOT NULL,
    [Name] NVARCHAR(256),
    [Email] NVARCHAR(128),
    [Phone] NVARCHAR(128),
    [Website] NVARCHAR(256),
    [Type] INTEGER NOT NULL,
    CONSTRAINT [PK_Group] PRIMARY KEY ([Id])
)
GO


/* ---------------------------------------------------------------------- */
/* Add table "CategoryForum"                                              */
/* ---------------------------------------------------------------------- */

CREATE TABLE [CategoryForum] (
    [Id] BIGINT IDENTITY(0,1) NOT NULL,
    [Name] NVARCHAR(256),
    CONSTRAINT [PK_CategoryForum] PRIMARY KEY ([Id])
)
GO


/* ---------------------------------------------------------------------- */
/* Add table "[User]"                                                     */
/* ---------------------------------------------------------------------- */

CREATE TABLE [User] (
    [Id] BIGINT IDENTITY(0,1) NOT NULL,
    [FirstName] NVARCHAR(128),
    [LastName] NVARCHAR(128),
    [Email] NVARCHAR(256) NOT NULL,
    [Password] NVARCHAR(512) NOT NULL,
    [IsLocked] BIT NOT NULL,
    [RegisterDate] DATETIME NOT NULL,
    [IsDeleted] BIT NOT NULL,
    [Phone] NVARCHAR(25),
    [Address] NVARCHAR(512),
    [Avartar] NVARCHAR(256),
    [Status] INTEGER NOT NULL,
    [RoleId] BIGINT NOT NULL,
    [GroupId] BIGINT NOT NULL,
    CONSTRAINT [PK_User] PRIMARY KEY ([Id])
)
GO


/* ---------------------------------------------------------------------- */
/* Add table "Thread"                                                     */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Thread] (
    [Id] BIGINT IDENTITY(0,1) NOT NULL,
    [Title] NVARCHAR(256),
    [CreatedDate] DATETIME NOT NULL,
    [CategoryId] BIGINT NOT NULL,
    [CreatorId] BIGINT NOT NULL,
    CONSTRAINT [PK_Thread] PRIMARY KEY ([Id])
)
GO


/* ---------------------------------------------------------------------- */
/* Add table "TestResult"                                                 */
/* ---------------------------------------------------------------------- */

CREATE TABLE [TestResult] (
    [Id] BIGINT IDENTITY(0,1) NOT NULL,
    [UserId] BIGINT NOT NULL,
    [TestId] BIGINT NOT NULL,
    [Results] XML,
    [StartTime] DATETIME NOT NULL,
    [EndTime] DATETIME,
    [TotalMark] FLOAT NOT NULL,
    [ResultMark] FLOAT,
    [Note] NVARCHAR(256),
    CONSTRAINT [PK_TestResult] PRIMARY KEY ([Id])
)
GO


/* ---------------------------------------------------------------------- */
/* Add table "Post"                                                       */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Post] (
    [Id] BIGINT IDENTITY(0,1) NOT NULL,
    [Content] NTEXT,
    [UserId] BIGINT NOT NULL,
    [ThreadId] BIGINT NOT NULL,
    [CreatedDate] DATETIME NOT NULL,
    [Status] INTEGER NOT NULL,
    CONSTRAINT [PK_Post] PRIMARY KEY ([Id])
)
GO


/* ---------------------------------------------------------------------- */
/* Add table "Menu"                                                       */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Menu] (
    [Id] BIGINT IDENTITY(0,1) NOT NULL,
    [Name] NVARCHAR(128),
    [Url] NVARCHAR(256),
    [Content] NTEXT,
    [Order] INTEGER NOT NULL,
    [Type] INTEGER NOT NULL,
    [ForRole] BIGINT,
    [ParentMenuId] BIGINT,
    CONSTRAINT [PK_Menu] PRIMARY KEY ([Id])
)
GO


/* ---------------------------------------------------------------------- */
/* Add table "Question"                                                   */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Question] (
    [Id] BIGINT IDENTITY(0,1) NOT NULL,
    [Sumary] NVARCHAR(512),
    [Content] NTEXT,
    [Note] NVARCHAR(512),
    [Mark] FLOAT NOT NULL,
    [Type] INTEGER NOT NULL,
    [ShareType] INTEGER NOT NULL,
    [Status] INTEGER NOT NULL,
    [Level] INTEGER NOT NULL,
    [CreatedDate] DATETIME NOT NULL,
    [ParentQuestionId] BIGINT,
    [CreatorId] BIGINT NOT NULL,
    CONSTRAINT [PK_Question] PRIMARY KEY ([Id])
)
GO


/* ---------------------------------------------------------------------- */
/* Add table "Answer"                                                     */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Answer] (
    [Id] BIGINT IDENTITY(0,1) NOT NULL,
    [Sumary] NVARCHAR(512),
    [Content] NVARCHAR(512),
    [IsTrue] BIT NOT NULL,
    [AnswerSugestion] NVARCHAR(512),
    [Note] NVARCHAR(512),
    [QuestionId] BIGINT,
    CONSTRAINT [PK_Answer] PRIMARY KEY ([Id])
)
GO


/* ---------------------------------------------------------------------- */
/* Add table "TestQuestion"                                               */
/* ---------------------------------------------------------------------- */

CREATE TABLE [TestQuestion] (
    [Id] BIGINT IDENTITY(0,1) NOT NULL,
    [QuestionId] BIGINT NOT NULL,
    [TestId] BIGINT NOT NULL,
    CONSTRAINT [PK_TestQuestion] PRIMARY KEY ([Id])
)
GO


/* ---------------------------------------------------------------------- */
/* Foreign key constraints                                                */
/* ---------------------------------------------------------------------- */

ALTER TABLE [Menu] ADD CONSTRAINT [Menu_Menu] 
    FOREIGN KEY ([ParentMenuId]) REFERENCES [Menu] ([Id])
GO


ALTER TABLE [Question] ADD CONSTRAINT [Question_Question] 
    FOREIGN KEY ([ParentQuestionId]) REFERENCES [Question] ([Id])
GO


ALTER TABLE [Question] ADD CONSTRAINT [Subject_Question] 
    FOREIGN KEY ([Id]) REFERENCES [Subject] ([Id])
GO


ALTER TABLE [Question] ADD CONSTRAINT [User_Question] 
    FOREIGN KEY ([CreatorId]) REFERENCES [User] ([Id])
GO


ALTER TABLE [Answer] ADD CONSTRAINT [Question_Answer] 
    FOREIGN KEY ([QuestionId]) REFERENCES [Question] ([Id])
GO


ALTER TABLE [TestSubject] ADD CONSTRAINT [Subject_TestSubject] 
    FOREIGN KEY ([SubjectId]) REFERENCES [Subject] ([Id])
GO


ALTER TABLE [TestSubject] ADD CONSTRAINT [Test_TestSubject] 
    FOREIGN KEY ([TestId]) REFERENCES [Test] ([Id])
GO


ALTER TABLE [TestQuestion] ADD CONSTRAINT [Question_TestQuestion] 
    FOREIGN KEY ([QuestionId]) REFERENCES [Question] ([Id])
GO


ALTER TABLE [TestQuestion] ADD CONSTRAINT [Test_TestQuestion] 
    FOREIGN KEY ([TestId]) REFERENCES [Test] ([Id])
GO


ALTER TABLE [User] ADD CONSTRAINT [Role_User] 
    FOREIGN KEY ([RoleId]) REFERENCES [Role] ([Id])
GO


ALTER TABLE [User] ADD CONSTRAINT [Group_User] 
    FOREIGN KEY ([GroupId]) REFERENCES [Group] ([Id])
GO


ALTER TABLE [Post] ADD CONSTRAINT [User_Post] 
    FOREIGN KEY ([UserId]) REFERENCES [User] ([Id])
GO


ALTER TABLE [Post] ADD CONSTRAINT [Thread_Post] 
    FOREIGN KEY ([ThreadId]) REFERENCES [Thread] ([Id]) ON DELETE CASCADE
GO


ALTER TABLE [Thread] ADD CONSTRAINT [User_Thread] 
    FOREIGN KEY ([CreatorId]) REFERENCES [User] ([Id])
GO


ALTER TABLE [Thread] ADD CONSTRAINT [CategoryForum_Thread] 
    FOREIGN KEY ([CategoryId]) REFERENCES [CategoryForum] ([Id]) ON DELETE CASCADE
GO


ALTER TABLE [TestResult] ADD CONSTRAINT [User_TestResult] 
    FOREIGN KEY ([UserId]) REFERENCES [User] ([Id])
GO


ALTER TABLE [TestResult] ADD CONSTRAINT [Test_TestResult] 
    FOREIGN KEY ([TestId]) REFERENCES [Test] ([Id])
GO

